package com.kurumi.util;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * ExcelUtil
 * 
 * @author liyutao
 *
 */
public class ExcelUtil {

	public static void exportExcel(OutputStream out, String title, String[] headers, List<String> keys,
			List<Map<String, Object>> datas) {

		try {

			// 创建一个工作薄
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 创建一个sheet页
			HSSFSheet sheet = workbook.createSheet();
			// 设置sheet页的列宽
			sheet.setDefaultColumnWidth(20);

			// 设置标题样式
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			// 设置字体居中显示
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 设置标题字体
			HSSFFont titleFont = workbook.createFont();
			titleFont.setFontHeightInPoints((short) 25);
			titleStyle.setFont(titleFont);

			// 产生标题
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = row.createCell(0);
			cell.setCellStyle(titleStyle);
			cell.setCellValue(title);
			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));

			// 表头样式
			HSSFCellStyle headStyle = workbook.createCellStyle();
			headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
			// 设置填充模式
			headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			// 设置字体居中
			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			// 设置字体样式
			HSSFFont headFont = workbook.createFont();
			headFont.setColor(HSSFColor.LIGHT_ORANGE.index);
			headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体粗细
			headFont.setFontHeightInPoints((short) 16);
			headStyle.setFont(headFont);
			// 创建第二行，生成表格的表头行
			row = sheet.createRow(1);// 第二行
			for (int i = 0; i < headers.length; i++) {
				cell = row.createCell(i);// 从第一列开始
				cell.setCellStyle(headStyle);// 设置列的样式
				cell.setCellValue(headers[i]);
			}
			int i = 0;
			for (Map<String, Object> data : datas) {
				row = sheet.createRow(2 + i);// 从第三行去写数据

				for (int colIndex = 0; colIndex < keys.size(); colIndex++) {
					cell = row.createCell(colIndex);
					Object value = data.get(keys.get(colIndex));
					if (value instanceof String) {
						cell.setCellValue((String) value);
					} else if (value instanceof Integer) {
						cell.setCellValue((Integer) value);
					} else if (value instanceof Date) {
						// cell.setCellValue(DateUtil.dateFormat((Date)value));
					} else {
						cell.setCellValue(value.toString());
					}

				}
				i++;
			}

			workbook.write(out);

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	
	/**
	 * 导出excel数据
	 * 
	 * @param filePath
	 * @return
	 */
	public static void exportExcel(OutputStream out, String[] header, List<Map<String, Object>> mapList) {
		// 创建一个工作薄
		HSSFWorkbook workbook = null;
		try {

			// 创建一个工作薄
			workbook = new HSSFWorkbook();
			// 创建一个sheet页
			HSSFSheet sheet = workbook.createSheet();
			// 设置sheet页的列宽
			sheet.setDefaultColumnWidth(20);

			// 设置标题样式
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			// 设置字体居中显示
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 设置标题字体
			HSSFFont titleFont = workbook.createFont();
			titleFont.setFontHeightInPoints((short) 25);
			titleStyle.setFont(titleFont);
			
			HSSFRow row = null;
			HSSFCell cell = null;

//			// 产生标题
//			HSSFRow row = sheet.createRow(0);
//			HSSFCell cell = row.createCell(0);
//			cell.setCellStyle(titleStyle);
//			cell.setCellValue(title);
//			// 合并单元格
//			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));

			// 表头样式
			HSSFCellStyle headStyle = workbook.createCellStyle();
			headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
			// 设置填充模式
			headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			// 设置字体居中
			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			// 设置字体样式
			HSSFFont headFont = workbook.createFont();
			// headFont.setColor(HSSFColor.LIGHT_ORANGE.index);
			headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体粗细
			headFont.setFontHeightInPoints((short) 16);
			headStyle.setFont(headFont);
			// 创建第二行，生成表格的表头行
			row = sheet.createRow(0);// 第二行
			for (int i = 0; i < header.length; i++) {
				cell = row.createCell(i);// 从第一列开始
				cell.setCellStyle(headStyle);// 设置列的样式
				cell.setCellValue(header[i]);
			}
			int i = 0;
			for (Map<String, Object> cellMap : mapList) {
				row = sheet.createRow(1 + i);// 从第三行去写数据
				int j = 0;
				for (Map.Entry<String, Object> entry : cellMap.entrySet()) {
					cell = row.createCell(j);
					Object obj = entry.getValue();
					if (obj instanceof Integer) {
						cell.setCellValue((Integer) entry.getValue());
					}
					if (obj instanceof Date) {
						 cell.setCellValue(DateUtil.DateToStr("yyyy-MM-dd HH:mm:ss",(Date)entry.getValue()));
					}
					if (obj instanceof String) {
						cell.setCellValue((String) entry.getValue());
					}
					if (obj instanceof Double) {
						cell.setCellValue((Double) entry.getValue());
					}
					if (obj instanceof Float) {
						cell.setCellValue((Float) entry.getValue());
					}
					if (obj instanceof Long) {
						cell.setCellValue((Long) entry.getValue());
					}

					j++;
				}
				i++;
			}

			workbook.write(out);
			out.flush();
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	

	/**
	 * 导出excel数据
	 * 
	 * @param filePath
	 * @return
	 */
	public static void exportExcel(OutputStream out, String title, String[] header, List<Map<String, Object>> mapList) {
		// 创建一个工作薄
		HSSFWorkbook workbook = null;
		try {

			// 创建一个工作薄
			workbook = new HSSFWorkbook();
			// 创建一个sheet页
			HSSFSheet sheet = workbook.createSheet();
			// 设置sheet页的列宽
			sheet.setDefaultColumnWidth(20);

			// 设置标题样式
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			// 设置字体居中显示
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 设置标题字体
			HSSFFont titleFont = workbook.createFont();
			titleFont.setFontHeightInPoints((short) 25);
			titleStyle.setFont(titleFont);

			// 产生标题
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = row.createCell(0);
			cell.setCellStyle(titleStyle);
			cell.setCellValue(title);
			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length - 1));

			// 表头样式
			HSSFCellStyle headStyle = workbook.createCellStyle();
			headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
			// 设置填充模式
			headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			// 设置字体居中
			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			// 设置字体样式
			HSSFFont headFont = workbook.createFont();
			// headFont.setColor(HSSFColor.LIGHT_ORANGE.index);
			headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体粗细
			headFont.setFontHeightInPoints((short) 16);
			headStyle.setFont(headFont);
			// 创建第二行，生成表格的表头行
			row = sheet.createRow(1);// 第二行
			for (int i = 0; i < header.length; i++) {
				cell = row.createCell(i);// 从第一列开始
				cell.setCellStyle(headStyle);// 设置列的样式
				cell.setCellValue(header[i]);
			}
			int i = 0;
			for (Map<String, Object> cellMap : mapList) {
				row = sheet.createRow(2 + i);// 从第三行去写数据
				int j = 0;
				for (Map.Entry<String, Object> entry : cellMap.entrySet()) {
					cell = row.createCell(j);
					Object obj = entry.getValue();
					if (obj instanceof Integer) {
						cell.setCellValue((Integer) entry.getValue());
					}
					if (obj instanceof Date) {
						 cell.setCellValue(DateUtil.DateToStr("yyyy-MM-dd HH:mm:ss",(Date)entry.getValue()));
					}
					if (obj instanceof String) {
						cell.setCellValue((String) entry.getValue());
					}
					if (obj instanceof Double) {
						cell.setCellValue((Double) entry.getValue());
					}
					if (obj instanceof Float) {
						cell.setCellValue((Float) entry.getValue());
					}
					if (obj instanceof Long) {
						cell.setCellValue((Long) entry.getValue());
					}

					j++;
				}
				i++;
			}

			workbook.write(out);
			out.flush();
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 导出excel数据
	 * 
	 * @param filePath
	 * @return
	 */
	public static void exportExcel(OutputStream out, List<Map<String, Object>> mapList) {

/*		String[] header = { "识别码", "病案号", "患者姓名", "身份证号", "身高", "体重", "腰围", "入院方式", "居住方式", "需要人照顾", "主要照顾者", "社会家庭关怀",
				"病毒性肝炎", "病毒肝炎患病时间", "结核", "结核所患时间", "外科手术史名称", "外科手术史时间", "重大外伤史外伤情况", "重大外伤史受伤时间", "重大外伤史愈合情况",
				"预防接种史", "接种疫苗", "输血史", "输血时间", "输血类型", "过敏源", "糖尿病类型", "糖尿病时间", "糖尿病用药", "高血压患病时间", "高血压用药", "高血脂患病时间",
				"高血脂用药", "-冠心病患病时间", "冠心病用药", "冠心病近期心绞痛发作", "免疫抑制剂治疗用药时间", "免疫抑制剂治疗药名", "肿瘤名称", "肿瘤患病时间", "肿瘤治疗方式",
				"前列腺增生患病时间", "前列腺增生用药", "放射性物质", "所接触放射性物质、有毒有害物质名称", "吸烟情况", "吸烟年数", "每日吸烟数量", "戒烟年数", "饮酒情况",
				"饮酒量（ml/日）", "饮酒年数", "戒酒年数", "牲畜接触史", "牲畜接触种类", "牲畜接触时长", "治游史", "冶游次数", "最近一次治游时间", "绝经", "初潮年龄",
				"月经持续时间（天）", "月经间隔天", "孕次", "产次", "流产次数", "存活次数", "子女健康", "亲属患有类似疾病", "患病亲属关系", "其他遗传病、传染病家族史", "血尿",
				"疼痛", "疼痛区域", "查体偶然发现", "转移灶症状", "副瘤综合征", "腹部包块", "腹部肿瘤", "肾区叩击痛", "输尿管走行区压痛",
				"腔静脉瘤栓评估(Mayo Clinic 五级分类*)" };*/
		// 创建一个工作薄
		HSSFWorkbook workbook = null;
		try {

			// 创建一个工作薄
			workbook = new HSSFWorkbook();
			HSSFSheet sheet = null;
			for (Map<String, Object> dataMap : mapList) {
				
				String[] header = (String[]) dataMap.get("header");
				
				List<Map<String,Object>> dataMapList = (List<Map<String, Object>>) dataMap.get("data");

				// 创建一个sheet页
				sheet = workbook.createSheet((String)dataMap.get("sheetName"));
				// 设置sheet页的列宽
				sheet.setDefaultColumnWidth(20);

				// 设置标题样式
				HSSFCellStyle titleStyle = workbook.createCellStyle();
				// 设置字体居中显示
				titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				// 设置标题字体
				HSSFFont titleFont = workbook.createFont();
				titleFont.setFontHeightInPoints((short) 25);
				titleStyle.setFont(titleFont);
				HSSFRow row = null;
				HSSFCell cell = null;

				// 表头样式
				HSSFCellStyle headStyle = workbook.createCellStyle();
				headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
				// 设置填充模式
				headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

				// 设置字体居中
				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
				headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
				// 设置字体样式
				HSSFFont headFont = workbook.createFont();
				// headFont.setColor(HSSFColor.LIGHT_ORANGE.index);
				// headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体粗细
				headFont.setFontHeightInPoints((short) 16);
				headStyle.setFont(headFont);
				// 创建第二行，生成表格的表头行
				row = sheet.createRow(0);// 第二行
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);// 从第一列开始
					// cell.setCellStyle(headStyle);// 设置列的样式
					cell.setCellValue(header[i]);
				}
				int i = 0;
				for (Map<String, Object> cellMap : dataMapList) {
					row = sheet.createRow(1 + i);// 从第三行去写数据
					int j = 0;
					for (Map.Entry<String, Object> entry : cellMap.entrySet()) {
						cell = row.createCell(j);
						Object obj = entry.getValue();
						String key = entry.getKey();
						if (obj instanceof Integer) {
							cell.setCellValue((Integer) entry.getValue());
						} else if (obj instanceof Date) {
							 cell.setCellValue(DateUtil.DateToStr("yyyy-MM-dd",(Date)entry.getValue()));
						} else if (obj instanceof String) {
							cell.setCellValue((String) entry.getValue());
						} else if (obj instanceof Double) {
							cell.setCellValue((Double) entry.getValue());
						} else if (obj instanceof Float) {
							cell.setCellValue((Float) entry.getValue());
						} else if (obj instanceof Long) {
							cell.setCellValue((Long) entry.getValue());
						} else if (obj instanceof Boolean) {
							cell.setCellValue((Boolean) entry.getValue());
						} else {
							if (entry.getValue() != null) {
								BigDecimal decimal = (BigDecimal) entry.getValue();
								cell.setCellValue(decimal.toString());
							}

						}

						j++;
					}
					i++;
				}

			}

			workbook.write(out);
			out.flush();
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}
